package com.actionsky.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.actionsky.entity.RssItem;

public class RssItemDAO {
	private static Log _log = LogFactory.getLog(RssItem.class);
	private static int count = 10;
	
	/** 订阅最新10条文章 **/
	public static List<RssItem> findNewDocuments(int type){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<RssItem> list = new ArrayList<RssItem>();
		String sql = "select id,title,link,pubdate,description from t_rss where type=? order by pubdate desc limit ?";
		try{
			pst = con.prepareStatement(sql);
			pst.setInt(1, type);
			pst.setInt(2, count);
			rs = pst.executeQuery();
			while(rs.next()){
				RssItem rssItem = new RssItem();
				rssItem.setId(rs.getInt("id"));
				rssItem.setTitle(rs.getString("title"));
				rssItem.setLink(rs.getString("link"));
				rssItem.setPubdate(rs.getTimestamp("pubdate"));
				rssItem.setDescription(rs.getString("description"));
				list.add(rssItem);
			}
			return list;
		}catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
	public static List<RssItem> findChangedDocumentsByType(int type, int category){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<RssItem> list = new ArrayList<RssItem>();
		String sql = "select id,title,link,pubdate,description,type from t_rss where type=? and category=? order by pubdate desc";
		
		try{
			pst = con.prepareStatement(sql);
			pst.setInt(1, type);
			pst.setInt(2, category);
			rs = pst.executeQuery();
			while(rs.next()){
				RssItem rssItem = new RssItem();
				rssItem.setId(rs.getInt("id"));
				rssItem.setTitle(rs.getString("title"));
				rssItem.setLink(rs.getString("link"));
				rssItem.setPubdate(rs.getTimestamp("pubdate"));
				rssItem.setDescription(rs.getString("description"));
				rssItem.setType(rs.getInt("type"));
				list.add(rssItem);
			}
			return list;
		}catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
	/** 知识库首页默认num=20篇  **/
	public static List<RssItem> findChangedDocuments(int num){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<RssItem> list = new ArrayList<RssItem>();
		String sql = "select id,title,link,pubdate,description,type from t_rss order by pubdate desc limit ?";
		try{
			pst = con.prepareStatement(sql);
			pst.setInt(1, num);
			rs = pst.executeQuery();
			while(rs.next()){
				RssItem rssItem = new RssItem();
				rssItem.setId(rs.getInt("id"));
				rssItem.setTitle(rs.getString("title"));
				rssItem.setLink(rs.getString("link"));
				rssItem.setPubdate(rs.getTimestamp("pubdate"));
				rssItem.setDescription(rs.getString("description"));
				rssItem.setType(rs.getInt("type"));
				list.add(rssItem);
			}
			return list;
		}catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
	public static List<RssItem> findChangedDocumentsByCategory(int category){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<RssItem> list = new ArrayList<RssItem>();
		String sql = "select r.id,r.title,r.link,r.pubdate,r.description,k.summary,r.type from  t_kb_category as k left join t_rss as r on r.category=k.id where r.category=? order by r.pubdate desc";
		try{
			pst = con.prepareStatement(sql);
			pst.setInt(1, category);
			rs = pst.executeQuery();
			while(rs.next()){
				RssItem rssItem = new RssItem();
				rssItem.setId(rs.getInt("id"));
				rssItem.setTitle(rs.getString("title"));
				rssItem.setLink(rs.getString("link"));
				rssItem.setPubdate(rs.getTimestamp("pubdate"));
				rssItem.setDescription(rs.getString("description"));
				rssItem.setSummary(rs.getString("summary"));
				rssItem.setType(rs.getInt("type"));
				list.add(rssItem);
			}
			return list;
		}catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
}
